package model.persistencia;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

public class BancoDeDados {    
    
    public BancoDeDados() {
    } 
    
    public Connection getConexao(){
        Connection con = null;
        
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(BancoDeDados.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, "Driver JDBC não encontrado:\n"+ex.getMessage()+"\n","Erro",JOptionPane.ERROR_MESSAGE);
        }
        
        try {            
            con = DriverManager.getConnection("jdbc:postgresql://localhost/supermercado","postgres","admin");            
        } catch (SQLException ex) {
            Logger.getLogger(BancoDeDados.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, "Erro na conexção com o banco:\n"+ex.getMessage()+"\n","Erro",JOptionPane.ERROR_MESSAGE);
        }
        
        return con;
    }
    
    public Connection getConexaoCriaDB() throws Exception{
        Class.forName("com.mysql.jdbc.Driver");        
        Connection conex = null;
        conex = DriverManager.getConnection("jdbc:mysql://localhost/mysql", "root", "admin");
        return conex;
    }
    
    public void gerarBanco() throws Exception{
        
        BancoDeDados db = new BancoDeDados();        
        Connection conexao = db.getConexaoCriaDB();
       
        String banco = "CREATE DATABASE biblioteca"; 
        PreparedStatement ps = conexao.prepareStatement(banco); 
        ps.execute();        
        conexao.close();
        
        Connection con = db.getConexao();
        
        String sqlUsuario = "CREATE TABLE usuario_biblioteca (" +
                "login varchar(255), " +
                "senha varchar(255), " +
                "tipo_usuario varchar(100));";
        PreparedStatement pStmtUsuario = con.prepareStatement(sqlUsuario);
        pStmtUsuario.execute();
        
        String sqLivro ="" +
                "CREATE TABLE  `livro` (  " +
                    "`cod_livro` int unsigned NOT NULL auto_increment,  " +
                    "`titulo` varchar(100) NOT NULL,  " +
                    "`autor` varchar(100) NOT NULL,  " +
                    "`isbn` varchar(20) NOT NULL,  " +
                    "`editora` varchar(20) NOT NULL,  " +
                    "`edicao` varchar(20) NOT NULL,  " +
                    "`qtd_exemplares` smallint unsigned NOT NULL,  " +
                    "`area_conhecimento` varchar(100) NOT NULL,  " +
                    "`status_livro` varchar(45) NOT NULL,  " +
                    "`qtd_exemp_presente` smallint unsigned NOT NULL, " +
                    "`data_aquisicao` BIGINT NOT NULL,  " +
                 "PRIMARY KEY  (`cod_livro`, `isbn`));";
        PreparedStatement pStmtLivro = con.prepareStatement(sqLivro);
        pStmtLivro.execute();
        
        String sqlCurso = ""+
                "CREATE TABLE `curso` ("+
                    "`cod_curso` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"+
                    "`nome_curso` VARCHAR(255) NULL,"+
                "PRIMARY KEY(`cod_curso`));";
        PreparedStatement pStmtCurso = con.prepareStatement(sqlCurso);
        pStmtCurso.execute();
        
        String sqlTurma = ""+
            "CREATE TABLE `turma` ("+
                  "`cod_turma` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"+
                  "`curso_cod_curso` INTEGER UNSIGNED NOT NULL,"+
                  "`nome_turma` VARCHAR(45) NULL,"+
             "PRIMARY KEY(`cod_turma`),"+
             "FOREIGN KEY(`curso_cod_curso`)"+
             "REFERENCES `curso`(`cod_curso`)"+
             " ON DELETE NO ACTION"+
             " ON UPDATE NO ACTION);";
        PreparedStatement pStmtTurma = con.prepareStatement(sqlTurma);
        pStmtTurma.execute();
        
        String sqlLeitor = "" +
                "CREATE TABLE `leitor` ("+
                      "`cod_leitor` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,"+
                      "`nome` VARCHAR(100) NOT NULL,"+
                      "`cpf` VARCHAR(20) NOT NULL,"+
                      "`rg` VARCHAR(20) NOT NULL,"+
                      "`sexo` VARCHAR(20) NOT NULL,"+
                      "`data_nasc` BIGINT NOT NULL,"+
                      "`curso_cod_curso` INTEGER UNSIGNED NOT NULL,"+
                      "`turma_cod_turma` INTEGER UNSIGNED NOT NULL,"+
                      "`turno` VARCHAR(20) NOT NULL,"+
                      "`matricula` VARCHAR(20) NOT NULL,"+
                      "`logradouro` VARCHAR(100) NOT NULL,"+
                      "`bairro` VARCHAR(100) NOT NULL,"+
                      "`cidade` VARCHAR(100) NOT NULL,"+
                      "`cep` VARCHAR(20) NOT NULL,"+
                      "`uf` VARCHAR(2) NOT NULL,"+
                      "`email` VARCHAR(100) NULL,"+
                      "`telefone` VARCHAR(20) NULL,"+
                      "`celular` VARCHAR(20) NULL,"+
                      "`multa` BIGINT NULL,"+
                      "`status_leitor` VARCHAR(45) NOT NULL,"+
              "PRIMARY KEY(`cod_leitor`),"+
              "INDEX `leitor_FKIndex1`(`curso_cod_curso`), "+
              "INDEX `leitor_FKIndex2`(`turma_cod_turma`), "+
              "FOREIGN KEY(`curso_cod_curso`) "+
                "REFERENCES `curso`(`cod_curso`) " +
                  "ON DELETE NO ACTION " +
                  "ON UPDATE NO ACTION, " +
              "FOREIGN KEY(`turma_cod_turma`) "+
                "REFERENCES `turma`(`cod_turma`) "+
                  "ON DELETE NO ACTION "+
                  "ON UPDATE NO ACTION);";
        PreparedStatement pStmtLeitor = con.prepareStatement(sqlLeitor);
        pStmtLeitor.execute();    
        
        String sqlReserva = "" +
                "CREATE TABLE `reserva` ( " +
                    "`cod_res` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,  " +
                    "`livro_cod_livro` INTEGER UNSIGNED NOT NULL, " +
                    "`leitor_cod_leitor` INTEGER UNSIGNED NOT NULL, " +
                    "`data_res` BIGINT NOT NULL, " +
                    "`status_res` VARCHAR(45) NOT NULL, " +
                    "`data_prev_conclusao` bigint(20) NOT NULL,  " +
                    "`data_conclusao` bigint(20) default NULL,  " +
                "PRIMARY KEY  (`cod_res`),  " +
                "INDEX `reserva_FKIndex1`(`leitor_cod_leitor`),"+
                "INDEX `reserva_FKIndex2`(`livro_cod_livro`),"+
                "FOREIGN KEY(`leitor_cod_leitor`)"+
                    "REFERENCES `leitor`(`cod_leitor`)"+
                    "ON DELETE NO ACTION "+
                    "ON UPDATE NO ACTION,"+
                "FOREIGN KEY(`livro_cod_livro`)"+
                    "REFERENCES `livro`(`cod_livro`)"+
                    "ON DELETE NO ACTION "+
                    "ON UPDATE NO ACTION); ";
                
        PreparedStatement pStmtResv = con.prepareStatement(sqlReserva);
        pStmtResv.execute();
            
        String sqlEmprestimo = "" +
                "CREATE TABLE emprestimo (  " +
                    "cod_emp INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,  " +
                    "livro_cod_livro INTEGER UNSIGNED NOT NULL,  " +
                    "leitor_cod_leitor INTEGER UNSIGNED NOT NULL,  " +
                    "dias_atraso SMALLINT default NULL,  " +
                    "data_emp BIGINT NOT NULL,  " +
                    "data_prev_dev BIGINT NOT NULL,  " +
                    "status_emp    varchar(25),  "+
                    "data_da_devolucao bigint default NULL, "+
                "PRIMARY KEY(cod_emp),  " +
                "INDEX emprestimo_FKIndex1(leitor_cod_leitor),  " +
                "INDEX emprestimo_FKIndex2(livro_cod_livro),  " +
                "FOREIGN KEY(leitor_cod_leitor)    REFERENCES leitor(cod_leitor)      ON DELETE NO ACTION      ON UPDATE NO ACTION,  " +
                "FOREIGN KEY(livro_cod_livro)    REFERENCES livro(cod_livro)      ON DELETE NO ACTION      ON UPDATE NO ACTION);";
        PreparedStatement pStmtEmprestimo = con.prepareStatement(sqlEmprestimo);
        pStmtEmprestimo.execute();        
    
        String viewQdexemplares = "CREATE VIEW `qtd_exemplares_e_qtd_exemplares_presentes` AS SELECT " +
                "livro.`qtd_exemp_presente`, "+  
                "livro.`qtd_exemplares`, "+
                "livro.`cod_livro` "+
            "FROM" +
                "`livro` livro";
        PreparedStatement pStmtview1 = con.prepareStatement(viewQdexemplares);
        pStmtview1.execute();
               
        String viewLivrosEmprestados = "CREATE VIEW `livros_emprestados` AS select " +
                "livro.`cod_livro`,     " +
                "livro.`titulo`,    " +
                "livro.`autor`,     " +
                "livro.`isbn` ,     " +
                "livro.`editora`,     " +
                "livro.`edicao`,     " +
                "livro.`qtd_exemplares` ,     " +
                "livro.`area_conhecimento` ,     " +
                "livro.`status_livro` ,     " +
                "livro.`data_aquisicao` ,     " +
                "livro.`qtd_exemp_presente`" +
            "FROM     " +
                "`livro` livro INNER JOIN `emprestimo` emprestimo ON livro.`cod_livro` = emprestimo.`livro_cod_livro`;";    
        PreparedStatement pStmtview2 = con.prepareStatement(viewLivrosEmprestados);
        pStmtview2.execute();

      String viewLivrosReservados = "CREATE VIEW `livros_reservados` AS select " +
              "	livro.`cod_livro`, " +
              " livro.`titulo`," +
              " livro.`autor`," +
              "	livro.`isbn` ," +
              " livro.`editora`," +
              " livro.`edicao`, " +
              " livro.`qtd_exemplares` ," +
              " livro.`area_conhecimento` ," +
              " livro.`status_livro` ," +              
              " livro.`data_aquisicao` ," +
              " livro.`qtd_exemp_presente` " +
          "FROM" +              
              " `livro` livro INNER JOIN `reserva` reserva ON livro.`cod_livro` = reserva.`livro_cod_livro`;";
      PreparedStatement pStmtview3 = con.prepareStatement(viewLivrosReservados);
        pStmtview3.execute();
      
               
    String viewQtdEmprestimoLivro = " CREATE VIEW qtd_emprestimo_livro AS SELECT " +
            "   Count(*) as qtd_emprestimo," +
            "   livro.`cod_livro`," +
            "   livro.`titulo`," +
            "   livro.`autor`," +
            "   livro.`isbn`," +
            "   livro.`editora`," +
            "   livro.`edicao`," +
            "   livro.`qtd_exemplares`," +
            "   livro.`area_conhecimento`," +
            "   livro.`status_livro`," +
            "   livro.`qtd_exemp_presente`," +
            "   livro.`data_aquisicao`," +
            "   emprestimo.`status_emp`" +
            "FROM" +
            "   `livro` livro INNER JOIN `emprestimo` emprestimo ON livro.`cod_livro` = emprestimo.`livro_cod_livro` " +
            "WHERE " +
            "   status_emp <> 'Devolvido' "+
            "GROUP BY" +
            "     cod_livro";    
    PreparedStatement pStmtview4 = con.prepareStatement(viewQtdEmprestimoLivro);
        pStmtview4.execute();
    
    String viewQtdReservaLivro = "CREATE VIEW qtd_reserva_livro AS SELECT " +
            "     Count(*)as qtd_reservas," +
            "     livro.`cod_livro`," +
            "     livro.`titulo`," +
            "     livro.`autor`," +
            "     livro.`isbn`," +
            "     livro.`editora`," +
            "     livro.`edicao`," +
            "     livro.`qtd_exemplares`," +
            "     livro.`area_conhecimento`," +
            "     livro.`status_livro`," +
            "     livro.`qtd_exemp_presente`," +
            "     livro.`data_aquisicao`," +
            "     reserva.`status_res`" +
            "FROM" +
            "     `livro` livro INNER JOIN `reserva` reserva ON livro.`cod_livro` = reserva.`livro_cod_livro`" +
            "WHERE " +
            "     status_res <> 'Conclu�da' "+
            "GROUP BY" +
            "     cod_livro";
    PreparedStatement pStmtview5 = con.prepareStatement(viewQtdReservaLivro);
        pStmtview5.execute();
        con.close(); 

    }
       
    public void dropBanco() throws Exception{
        BancoDeDados db = new BancoDeDados();        
        Connection conexao = db.getConexaoCriaDB();
        
        String dropDB = "DROP DATABASE IF EXISTS biblioteca";
        PreparedStatement ps = conexao.prepareStatement(dropDB); 
        ps.execute();
        conexao.close();
    
    }
    
}
